package cn.dceast.common.utils;

import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.CSVWriter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.charset.Charset;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by fanbo on 2017-05-23.
 */
public class ExcelDataUtil {

    public static List<Map<String,List>> readExcelContent(String path,MultipartFile multipartFile){
        List<Map<String,List>> returnList = new ArrayList<>();
        Map<String,List> returnMap = new HashMap<>();


        InputStream is = null;
        Workbook wb = null;
        Sheet sheet;
        Row row;
        try {
            List<Object[]> titleList = new ArrayList<>();
            List<Object[]> dataList = new ArrayList<>();
            if(path.endsWith("csv")){
                File csv = new File(path);
                //生成CsvReader对象，以，为分隔符，GBK编码方式
                CSVReader reader = new CSVReader(new InputStreamReader(new FileInputStream(csv), Charset.forName("UTF-8")), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
                String [] header = reader.readNext();   //用readnext读取之后就不存在于stream中了
                Object[] titleObj = new Object[header.length];
                int si=0;
                for (String s : header) {

                    Map<String, Object> titlelMap = new HashMap<>();
                    titlelMap.put("titleName", s);
                    titlelMap.put("cloumName", s);
                    titleObj[si] = titlelMap;
                    si++;
                }
                titleList.add(titleObj);

                List<String[]> list = reader.readAll(); //此时读取的已经是第二行了
                for (String[] dataObj:list){
                    dataList.add(dataObj);
                }
            }else {
                if (path.endsWith("xls")) {
                    //2003
                    is = new FileInputStream(path);
                    wb = new HSSFWorkbook(is);
                } else if (path.endsWith("xlsx")) {
                    //2007
                    wb = new XSSFWorkbook(path);
                }
                sheet = wb.getSheetAt(0);
                // 得到总行数
                int rowNum = sheet.getLastRowNum();
                if (rowNum < 1) {
                    return null;
                }
                row = sheet.getRow(0);

                for (int i = 0; i <= rowNum; i++) {
                    row = sheet.getRow(i);
                    if (isBlankRow(row)) {//如果是空行
                        break;
                    }
                    int colNum = row.getPhysicalNumberOfCells();
                    int j = 0;
                    Object[] titleObj = new Object[colNum];
                    Object[] dataObj = new Object[colNum];
                    while (j < colNum) {
                        if (i == 0) {
                            //将数据表头存储起来。
                            Map<String, Object> titlelMap = new HashMap<>();
                            titlelMap.put("titleName", getCellFormatValue(row.getCell((short) j)).trim());
                            titlelMap.put("cloumName", "cloum" + j);
                            titleObj[j] = titlelMap;
                        } else {
                            //取数据表具体数据
                            dataObj[j] = getCellFormatValue(row.getCell((short) j)).trim();
                        }
                        j++;
                    }
                    if (i == 0) {
                        titleList.add(titleObj);
                    } else {
                        dataList.add(dataObj);
                    }
                }
            }
            returnMap.put("title", titleList);
            returnMap.put("data", dataList);

            returnList.add(returnMap);
            return returnList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     */
    private static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {

                case Cell.CELL_TYPE_NUMERIC: {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// 处理日期格式、时间格式
                        double value = cell.getNumericCellValue();
                        short format = cell.getCellStyle().getDataFormat();
                        SimpleDateFormat sdf = null;
                        if(format == 14 || format == 31 || format == 57 || format == 58){
                            //日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                            cellvalue = sdf.format(date);
                        }else if (format == 20 || format == 32) {
                            //时间
                            sdf = new SimpleDateFormat("HH:mm");
                            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                            cellvalue = sdf.format(date);
                        }else if (format == 22) {
                            //时间
                            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                            cellvalue = sdf.format(date);
                        }else if (format == 21) {
                            //时间
                            sdf = new SimpleDateFormat("HH:mm:ss");
                            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                            cellvalue = sdf.format(date);
                        }else if (format == 17) {
                            //时间
                            sdf = new SimpleDateFormat("yyyy-MM");
                            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                            cellvalue = sdf.format(date);
                        }else{
                            DecimalFormat numFormat =new DecimalFormat("#.###");
                            cellvalue = numFormat.format(value);
                        }
                    } else {
                        double value = cell.getNumericCellValue();
                        CellStyle style = cell.getCellStyle();
                        DecimalFormat format = new DecimalFormat();
                        String temp = style.getDataFormatString();
                        // 单元格设置成常规
                        if (temp.equals("General")) {
                            format.applyPattern("#");
                        }
                        cellvalue = format.format(value);
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case Cell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    cellvalue = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    cellvalue = " ";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellvalue = " ";
                    break;
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }


    /**
     * 判断excel 空行
     */
    public static boolean isBlankRow(Row row) {
        if (row == null)
            return true;
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell hcell = row.getCell(i);
            if (!isBlankCell(hcell))
                return false;
        }
        return true;
    }

    /**
     * 判断excel 空单元格
     */
    public static boolean isBlankCell(Cell hcell) {
        if (hcell == null)
            return true;
        hcell.setCellType(hcell.CELL_TYPE_STRING);
        String content = hcell.getStringCellValue().trim();
        if (content == null || "".equals(content)) // 找到非空行
        {
            return true;
        }
        return false;
    }


    public static Workbook getWorkBook(MultipartFile file,InputStream is) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
//            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith("xls")){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith("xlsx")){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }


    // 验证字符串是否为正确路径名的正则表达式
//	private static String matches = "[A-Za-z]:\\\\[^:?\"><*]*";
    // 通过 sPath.matches(matches) 方法的返回值判断是否正确
    // sPath 为路径字符串
    private static boolean flag = false;
    private static File file = null;

    public static boolean DeleteFolder(String sPath) {
        flag = false;
        file = new File(sPath);
        // 判断目录或文件是否存在
        if (!file.exists()) {  // 不存在返回 false
            return flag;
        } else {
            // 判断是否为文件
            if (file.isFile()) {  // 为文件时调用删除文件方法
                return deleteFile(sPath);
            } else {  // 为目录时调用删除目录方法
                return deleteDirectory(sPath);
            }
        }
    }

    public static boolean deleteFile(String sPath) {
        flag = false;
        file = new File(sPath);
        // 路径为文件且不为空则进行删除
        if (file.isFile() && file.exists()) {
            file.delete();
            flag = true;
        }
        return flag;
    }

    public static boolean deleteDirectory(String sPath) {
        //如果sPath不以文件分隔符结尾，自动添加文件分隔符
        if (!sPath.endsWith(File.separator)) {
            sPath = sPath + File.separator;
        }
        File dirFile = new File(sPath);
        //如果dir对应的文件不存在，或者不是一个目录，则退出
        if (!dirFile.exists() || !dirFile.isDirectory()) {
            return false;
        }
        flag = true;
        //删除文件夹下的所有文件(包括子目录)
        File[] files = dirFile.listFiles();
        for (int i = 0; i < files.length; i++) {
            //删除子文件
            if (files[i].isFile()) {
                flag = deleteFile(files[i].getAbsolutePath());
                if (!flag) break;
            } //删除子目录
            else {
                flag = deleteDirectory(files[i].getAbsolutePath());
                if (!flag) break;
            }
        }
        if (!flag) return false;
        //删除当前目录
        if (dirFile.delete()) {
            return true;
        } else {
            return false;
        }
    }
}
